﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using RCREEE.Library.Entities;

namespace RCREEE.Library
{
    public partial class DataManager
    {


        #region Select

        public static List<TinyItem> GetAllUsers()
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "GetAllUsers";

            #region Parametrers
            _Command.Parameters.Clear();

            #endregion

            _DataSet = new DataSet();
            _Adapter.SelectCommand = _Command;
            _Adapter.Fill(_DataSet);

            List<TinyItem> users = new List<TinyItem>();

            DataTable siteMapNodesTable = _DataSet.Tables[0];
            if (siteMapNodesTable != null)
            {
                for (int i = 0; i < siteMapNodesTable.Rows.Count; i++)
                {
                    TinyItem user = new TinyItem();
                    if (siteMapNodesTable.Rows[i]["UserId"] != null && siteMapNodesTable.Rows[i]["FullName"] != null)
                    {
                        int itemUserId;
                        if (int.TryParse(siteMapNodesTable.Rows[i]["UserId"].ToString(), out itemUserId) &&
                            itemUserId > 0)
                        {
                            user.ID = itemUserId;

                        }
                        if (siteMapNodesTable.Rows[i]["FullName"] != null)
                        {
                            user.Name = siteMapNodesTable.Rows[i]["FullName"].ToString();
                        }

                    }

                    users.Add(user);
                }

            }
            return users;
        }

        public static User GetUserData(int userId, int langCode)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "GetUserData";

            #region Parametrers
            _Command.Parameters.Clear();
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@UserId",
                SqlDbType = SqlDbType.Int,
                Value = userId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@LanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = langCode,
            });
            #endregion

            _DataSet = new DataSet();
            _Adapter.SelectCommand = _Command;
            _Adapter.Fill(_DataSet);

            User user = new User();

            DataTable userDataTable = _DataSet.Tables[0];
            if (userDataTable != null && userDataTable.Rows.Count > 0)
            {
                if (userDataTable.Rows[0]["UserId"] != null && userDataTable.Rows[0]["FullName"] != null)
                {
                    int itemUserId;
                    if (int.TryParse(userDataTable.Rows[0]["UserId"].ToString(), out itemUserId) &&
                        itemUserId > 0)
                    {
                        user.UserId = itemUserId;

                    }
                    if (userDataTable.Rows[0]["FullName"] != null)
                    {
                        user.FullName = userDataTable.Rows[0]["FullName"].ToString();
                    }
                    if (userDataTable.Rows[0]["PhoneNumber"] != null)
                    {
                        user.PhoneNumber = userDataTable.Rows[0]["PhoneNumber"].ToString();
                    }
                    if (userDataTable.Rows[0]["MobileNumber"] != null)
                    {
                        user.MobileNumber = userDataTable.Rows[0]["MobileNumber"].ToString();
                    }
                    if (userDataTable.Rows[0]["Email"] != null)
                    {
                        user.Email = userDataTable.Rows[0]["Email"].ToString();
                    }
                    if (userDataTable.Rows[0]["UserName"] != null)
                    {
                        user.UserName = userDataTable.Rows[0]["UserName"].ToString();
                    }
                    if (userDataTable.Rows[0]["Password"] != null)
                    {
                        user.Password = userDataTable.Rows[0]["Password"].ToString();
                    }
                    bool isAdmin;
                    if (bool.TryParse(userDataTable.Rows[0]["IsAdmin"].ToString(), out isAdmin))
                    {
                        user.IsAdmin = isAdmin;
                    }
                }

            }
            user.UserPermissions = new List<UserPermission>();

            DataTable userPermissionsTable = _DataSet.Tables[1];
            if (userPermissionsTable != null)
            {
                for (int i = 0; i < userPermissionsTable.Rows.Count; i++)
                {
                    UserPermission permission = new UserPermission();
                    if (userPermissionsTable.Rows[i]["PermisionId"] != null && userPermissionsTable.Rows[i]["CountryId"] != null && userPermissionsTable.Rows[i]["CountryName"] != null)
                    {
                        int permissionId;
                        if (int.TryParse(userPermissionsTable.Rows[i]["PermisionId"].ToString(), out permissionId) &&
                            permissionId > 0)
                        {
                            permission.PermisionId = permissionId;
                        }
                        else
                        {
                            continue;
                        }
                        permission.Country = new TinyItem();
                        int countryId;
                        if (int.TryParse(userPermissionsTable.Rows[i]["CountryId"].ToString(), out countryId) &&
                            countryId > 0)
                        {
                            permission.Country.ID = permission.CountryId = countryId;

                        }
                        if (userPermissionsTable.Rows[i]["CountryName"] != null)
                        {
                            permission.Country.Name = userPermissionsTable.Rows[i]["CountryName"].ToString();
                        }
                        bool canAdd;
                        if (bool.TryParse(userPermissionsTable.Rows[i]["CanAdd"].ToString(), out canAdd))
                        {
                            permission.CanAdd = canAdd;
                        }
                        bool canUpdate;
                        if (bool.TryParse(userPermissionsTable.Rows[i]["CanUpdate"].ToString(), out canUpdate))
                        {
                            permission.CanUpdate = canUpdate;
                        }
                        bool canDelete;
                        if (bool.TryParse(userPermissionsTable.Rows[i]["CanDelete"].ToString(), out canDelete))
                        {
                            permission.CanDelete = canDelete;
                        }

                    }

                    user.UserPermissions.Add(permission);
                }
            }

            return user;
        }

        public static List<Item> GetItemsByLanguageAndCategoryasdfsadf(int languageCode, int? categoryId, int? itemId = null)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "GetItemsByLanguageAndCategory";

            _Command.Parameters.Clear();

            if (categoryId != null && categoryId.Value > 0)
            {
                _Command.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@CategoryId",
                    SqlDbType = SqlDbType.Int,
                    Value = categoryId.Value,
                });
            }

            if (itemId != null && itemId.Value > 0)
            {

                _Command.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@itemId",
                    SqlDbType = SqlDbType.Int,
                    Value = itemId,
                });
            }

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@LanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = languageCode,
            });


            _DataSet = new DataSet();
            _Adapter.SelectCommand = _Command;
            _Adapter.Fill(_DataSet);

            List<Item> items = new List<Item>();

            DataTable itemsTable = _DataSet.Tables[0];



            if (itemsTable != null)
            {

                // addition by joe
                //itemsTable.Columns["Name"].ColumnName = "itemName";

                for (int i = 0; i < itemsTable.Rows.Count; i++)
                {

                    if (itemsTable.Rows[i]["itemId"] != null && itemsTable.Rows[i]["itemName"] != null && itemsTable.Rows[i]["ItemCategoryId"] != null)
                    {
                        Item item = new Item();
                        int resultItemId;
                        if (int.TryParse(itemsTable.Rows[i]["itemId"].ToString(), out resultItemId) && resultItemId > 0)
                        {
                            item.ItemID = resultItemId;
                        }
                        item.Name = itemsTable.Rows[i]["itemName"].ToString();

                        int resultCatId;
                        if (int.TryParse(itemsTable.Rows[i]["ItemCategoryId"].ToString(), out resultCatId) && resultCatId > 0)
                        {
                            item.CategoryId = resultCatId;
                        }
                        items.Add(item);
                    }
                }
            }
            return items;
        }


        public static User GetUser(string userName, string password, int langCode)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "GetUserByUserNameAndPassword";

            #region Parametrers
            _Command.Parameters.Clear();
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@UserName",
                SqlDbType = SqlDbType.NVarChar,
                Value = userName,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Password",
                SqlDbType = SqlDbType.NVarChar,
                Value = userName,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@LanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = langCode,
            });
            #endregion

            _DataSet = new DataSet();
            _DataSet.Tables.Clear();
            _Adapter.SelectCommand = _Command;
            _Adapter.Fill(_DataSet);

            User user = new User();

            DataTable userDataTable = _DataSet.Tables[0];
            if (userDataTable != null && userDataTable.Rows.Count > 0)
            {
                if (userDataTable.Rows[0]["UserId"] != null && userDataTable.Rows[0]["FullName"] != null)
                {
                    int itemUserId;
                    if (int.TryParse(userDataTable.Rows[0]["UserId"].ToString(), out itemUserId) &&
                        itemUserId > 0)
                    {
                        user.UserId = itemUserId;

                    }
                    if (userDataTable.Rows[0]["FullName"] != null)
                    {
                        user.FullName = userDataTable.Rows[0]["FullName"].ToString();
                    }
                    if (userDataTable.Rows[0]["PhoneNumber"] != null)
                    {
                        user.PhoneNumber = userDataTable.Rows[0]["PhoneNumber"].ToString();
                    }
                    if (userDataTable.Rows[0]["MobileNumber"] != null)
                    {
                        user.MobileNumber = userDataTable.Rows[0]["MobileNumber"].ToString();
                    }
                    if (userDataTable.Rows[0]["Email"] != null)
                    {
                        user.Email = userDataTable.Rows[0]["Email"].ToString();
                    }
                    if (userDataTable.Rows[0]["UserName"] != null)
                    {
                        user.UserName = userDataTable.Rows[0]["UserName"].ToString();
                    }
                    if (userDataTable.Rows[0]["Password"] != null)
                    {
                        user.Password = userDataTable.Rows[0]["Password"].ToString();
                    }
                    bool isAdmin;
                    if (bool.TryParse(userDataTable.Rows[0]["IsAdmin"].ToString(), out isAdmin))
                    {
                        user.IsAdmin = isAdmin;
                    }
                }

            }
            user.UserPermissions = new List<UserPermission>();

            DataTable userPermissionsTable = _DataSet.Tables[1];
            if (userPermissionsTable != null)
            {
                for (int i = 0; i < userPermissionsTable.Rows.Count; i++)
                {
                    UserPermission permission = new UserPermission();
                    if (userPermissionsTable.Rows[i]["PermisionId"] != null && userPermissionsTable.Rows[i]["CountryId"] != null && userPermissionsTable.Rows[i]["CountryName"] != null)
                    {
                        int permissionId;
                        if (int.TryParse(userPermissionsTable.Rows[i]["PermisionId"].ToString(), out permissionId) &&
                            permissionId > 0)
                        {
                            permission.PermisionId = permissionId;
                        }
                        else
                        {
                            continue;
                        }
                        permission.UserId = user.UserId;
                        permission.Country = new TinyItem();
                        int countryId;
                        if (int.TryParse(userPermissionsTable.Rows[i]["CountryId"].ToString(), out countryId) &&
                            countryId > 0)
                        {
                            permission.Country.ID = permission.CountryId = countryId;

                        }
                        if (userPermissionsTable.Rows[i]["CountryName"] != null)
                        {
                            permission.Country.Name = userPermissionsTable.Rows[i]["CountryName"].ToString();
                        }
                        bool canAdd;
                        if (bool.TryParse(userPermissionsTable.Rows[i]["CanAdd"].ToString(), out canAdd))
                        {
                            permission.CanAdd = canAdd;
                        }
                        bool canUpdate;
                        if (bool.TryParse(userPermissionsTable.Rows[i]["CanUpdate"].ToString(), out canUpdate))
                        {
                            permission.CanUpdate = canUpdate;
                        }
                        bool canDelete;
                        if (bool.TryParse(userPermissionsTable.Rows[i]["CanDelete"].ToString(), out canDelete))
                        {
                            permission.CanDelete = canDelete;
                        }

                    }

                    user.UserPermissions.Add(permission);
                }
            }

            return user;
        }
        #endregion

        #region Insert


        public static int InsertUser(User user)
        {
            int userId = 0;
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "InsertUser";

            #region Parameters

            _Command.Parameters.Clear();

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@FullName",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.FullName,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@PhoneNumber",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.PhoneNumber,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@MobileNumber",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.MobileNumber,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Email",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.Email,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@UserName",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.UserName,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Password",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.Password,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@IsAdmin",
                SqlDbType = SqlDbType.Bit,
                Value = user.IsAdmin,
            });
            #endregion

            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteScalar();

                // Close Connection)
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();



                if (returnedValue != null)
                {
                    int.TryParse(returnedValue.ToString(), out userId);
                }
            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            user.UserId = userId;
            return userId;

        }


        private static int InsertPermission(UserPermission userPermission)
        {
            int permissionId = 0;
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "InsertUserPermission";

            #region Parameters

            _Command.Parameters.Clear();

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@UserId",
                SqlDbType = SqlDbType.Int,
                Value = userPermission.UserId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@CountryId",
                SqlDbType = SqlDbType.Int,
                Value = userPermission.CountryId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@CanAdd",
                SqlDbType = SqlDbType.Bit,
                Value = userPermission.CanAdd,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@CanUpdate",
                SqlDbType = SqlDbType.Bit,
                Value = userPermission.CanUpdate,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@CanDelete",
                SqlDbType = SqlDbType.Bit,
                Value = userPermission.CanDelete,
            });
            #endregion

            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteScalar();

                // Close Connection)
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();



                if (returnedValue != null)
                {
                    int.TryParse(returnedValue.ToString(), out permissionId);
                }
            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            userPermission.PermisionId = permissionId;
            return permissionId;
        }
        #endregion


        #region Edit

        public static void EditUser(User user)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "UpdateUser";

            #region Parameters

            _Command.Parameters.Clear();

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@UserId",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.UserId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@FullName",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.FullName,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@PhoneNumber",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.PhoneNumber,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@MobileNumber",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.MobileNumber,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Email",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.Email,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@UserName",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.UserName,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Password",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.Password,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@IsAdmin",
                SqlDbType = SqlDbType.Bit,
                Value = user.IsAdmin,
            });
            #endregion

            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteScalar();

                // Close Connection)
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

        }

        private static void EditPermission(UserPermission userPermission)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "UpdateUserPermission";

            #region Parameters

            _Command.Parameters.Clear();

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@PermissionId",
                SqlDbType = SqlDbType.Int,
                Value = userPermission.PermisionId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@CanAdd",
                SqlDbType = SqlDbType.Bit,
                Value = userPermission.CanAdd,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@CanUpdate",
                SqlDbType = SqlDbType.Bit,
                Value = userPermission.CanUpdate,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@CanDelete",
                SqlDbType = SqlDbType.Bit,
                Value = userPermission.CanDelete,
            });
            #endregion

            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteScalar();

                // Close Connection)
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();


            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

        }


        #endregion

        #region Save
        public static int SaveUser(User currentUser)
        {

            int userId;
            if (currentUser.UserId > 0)
            {
                EditUser(currentUser);
                userId = currentUser.UserId;
            }
            else
            {
                userId = InsertUser(currentUser);
            }
            foreach (UserPermission permission in currentUser.UserPermissions)
            {
                permission.UserId = userId;
                SavePermission(permission);
            }
            return userId;
        }
        public static int SavePermission(UserPermission userPermission)
        {

            int permisionId;
            if (userPermission.PermisionId > 0)
            {
                EditPermission(userPermission);
                permisionId = userPermission.PermisionId;
            }
            else
            {
                permisionId = InsertPermission(userPermission);
            }
            return permisionId;
        }


        #endregion

        public static void DeleteUser(int userId)
        {

            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "DeleteUser";

            _Command.Parameters.Clear();

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@UserId",
                SqlDbType = SqlDbType.Int,
                Value = userId,
            });

            _Adapter.DeleteCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
        }

        public static bool IsUserExist(User user)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "IsUserExists";

            #region Parametrers
            _Command.Parameters.Clear();
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@UserName",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.UserName,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@UserId",
                SqlDbType = SqlDbType.Int,
                Value = user.UserId,
            });
            #endregion

            _DataSet = new DataSet();
            _DataSet.Tables.Clear();
            _Adapter.SelectCommand = _Command;
            _Adapter.Fill(_DataSet);

            if (_DataSet.Tables.Count > 0 && _DataSet.Tables[0] != null && _DataSet.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            return false;
        }


        public static DataTable ToDataTable<T>(this IList<T> data)
        {
            PropertyDescriptorCollection props =
                TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                table.Columns.Add(prop.Name, prop.PropertyType);
            }
            object[] values = new object[props.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }
                table.Rows.Add(values);
            }
            return table;
        }

    }
}
